<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ page import="org.edu.system.DBConnect" %>
<%@ page import="java.sql.*" %>
<%
	Connection con = DBConnect.getCon();
	Statement stmt = null;
	ResultSet rs = null;
%>
<!DOCTYPE html>
<html>
<head>
<!-- 显示网格线 -->    
    <xml>    
                <x:ExcelWorkbook>    
                    <x:ExcelWorksheets>    
                        <x:ExcelWorksheet>    
                            <!-- <x:Name>工作表标题</x:Name>-->  
                            <x:WorksheetOptions>    
                                <x:Print>    
                                    <x:ValidPrinterInfo />    
                                </x:Print>    
                            </x:WorksheetOptions>    
                        </x:ExcelWorksheet>    
                    </x:ExcelWorksheets>    
                </x:ExcelWorkbook>    
            </xml>    
    <!-- 显示网格线 -->    
<meta charset="utf-8">
<title>打印成绩</title>
</head>
<body>
<center>
	<table>
	<%
		response.setContentType("application/vnd.ms-excel");  
	    response.setHeader("Content-Disposition", "inline; filename="+"grade.xls");  
	%>
	<% 
		String sql = null;
		ResultSet rs1 = null;
		sql = "SELECT stu_id,stu_name FROM student WHERE course_list LIKE \"%"+request.getParameter("lessonID")+",%\"";
		stmt = null;
		rs = null;
		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);//执行命令
		out.print("<tr><th>学号</th><th>姓名</th><th>成绩</th></tr>");
		while (rs.next())// 如果记录集非空，表明有匹配的用户ID
		{
			String stu_id = rs.getString("stu_id");
			out.print("<tr>");
			out.print("<td>"+rs.getString("stu_id")+"&nbsp;</td>");
			out.print("<td>"+rs.getString("stu_name")+"</td>");
			sql = "select grade from grade where lesson_id = '" + request.getParameter("lessonID") + "' and stu_id='"+stu_id+"'";
			System.out.println(sql);
			stmt = con.createStatement();
			rs1 = stmt.executeQuery(sql);//执行命令
			if(rs1.next())
			{
				out.print("<td>"+rs1.getString("grade")+"</td>");
			}
			out.print("</tr>");
		} 
	%>
	</table>
</center>
</body>
</html>